<?php

namespace app\service;

use app\model\EntityEpisodeRelationModel;
use app\model\EntityModel;
use app\model\ModuleModel;
use app\model\NoteModel;
use app\model\ProjectMemberModel;
use app\model\ProjectModel;
use app\model\ReviewFeedbackModel;
use app\model\StepCategoryModel;
use app\model\TaskModel;
use app\model\TaskRelationTagModel;
use app\model\VersionModel;
use app\tool\SingletonTrait;
use support\Request;
use think\facade\Db;
use think\Collection;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\db\Raw;
use think\Model;

class BIService
{
    use SingletonTrait;

    /**
     * 项目列表
     * @param int $tenantId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function projectList(int $tenantId)
    {
        $projectIdList = OptionsService::getInstance()->getBiConfigShowProjectIdList();
        if (empty($projectIdList)) {
            return [];
        }
        $projectList = ProjectModel::whereIn('id', $projectIdList)->field('*')->order('id asc')->select();
        if ($projectList->isEmpty()) {
            return [];
        }
        // 播出的项目情况
        $now = time();
        $playedProjectIdList = EntityModel::where("module_id", ModuleService::getInstance()->byCode('episode')['id'])
            ->whereIn('project_id', $projectIdList)
            ->whereRaw("get_json_string(`json`,'$.episode_plan_show_time') <= {$now} and get_json_string(`json`,'$.episode_plan_show_time') > 0")
            ->group("project_id")
            ->field('project_id')->column('project_id');

        // 制作中的项目
        $inprogressStatus = StatusService::getInstance()->byCode('inprogress');
        $makingProjectIdList = EntityModel::whereIn("project_id", $projectIdList)
            ->where("module_id", ModuleService::getInstance()->byCode('episode')['id'])
            ->where("get_json_string(`json`,'$.episode_status') = {$inprogressStatus['id']}")
            ->group('project_id')
            ->field('project_id')
            ->column('project_id');
        $biProjectConfigMap = OptionsService::getInstance()->selectBiProjectConfig($projectIdList);
        $res = [];
        foreach ($projectList as $project) {
            $statusText = "";
            if (in_array($project['id'], $playedProjectIdList)) {
                if ($project['drama_type'] == "year") {
                    $statusText = "serializing";
                } else {
                    $statusText = "playing";
                }
            } else {
                if (in_array($project['id'], $makingProjectIdList)) {
                    $statusText = "making";
                } else {
                    $statusText = "creative";
                }
            }
            $tmp = [
                'id' => $project['id'],
                'name' => $project['name'],
                'status_text' => $statusText,
                'preview_image_big' => '',
                'preview_image_middle' => '',
                'preview_image_small' => '',
            ];
            if (isset($biProjectConfigMap[$project['id']])) {
                $tmp['preview_image_big'] = $biProjectConfigMap[$project['id']]['project_preview_img']['big'] ?? '';
                $tmp['preview_image_middle'] = $biProjectConfigMap[$project['id']]['project_preview_img']['middle'] ?? '';
                $tmp['preview_image_small'] = $biProjectConfigMap[$project['id']]['project_preview_img']['small'] ?? '';
            }
            $res[] = $tmp;
        }
        return $res;
    }

    /**
     * 项目人力资源分布情况
     * @param $projectId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function projectManResourceDistribution($projectId)
    {
        $project = ProjectModel::where('id', $projectId)->find();
        $belongProjectList = ProjectModel::where('belong_project_id', $projectId)->select();
        $allProjectIdList = [$project['id']];
        $allTenantIdList = [$project['tenant_id']];
        $belongProjectTenantMap = [];
        $belongProjectTenantMap[$project['id']] = $project['tenant_id'];
        foreach ($belongProjectList as $belongProject) {
            $allProjectIdList[] = $belongProject['id'];
            $allTenantIdList[] = $belongProject['tenant_id'];
            $belongProjectTenantMap[$belongProject['id']] = $belongProject['tenant_id'];
        }

        $groupMemberByProject = TenantGroupService::getInstance()->getTenantGroupMemberByProject($project['tenant_id'], $allTenantIdList, "yes", "yes");

        $projectMemberCountList = ProjectMemberModel::whereIn("project_id", $allProjectIdList)
            ->group('project_id')->field('count(distinct user_id) AS count,project_id')->select();
        $tenantProjectMemberCountMap = [];
        foreach ($projectMemberCountList as $count) {
            if (empty($belongProjectTenantMap[$count['project_id']])) {
                continue;
            }
            $tenantProjectMemberCountMap[$belongProjectTenantMap[$count['project_id']]] = $count['count'];
        }

        $res = [
            'inside_man_number' => 0,
            'outside_man_number' => 0,
            'supplier_address_man_number_data' => [],
        ];
        $groupMemberIdMap = [];
        foreach ($groupMemberByProject['tenant_group_member_ids'] as $k => $v) {
            $groupMemberIdMap[$v] = true;
        }
        foreach ($groupMemberByProject['tenant_address'] as $tempTenantId => $tempTenantAddressInfo) {
            $tempMemberCount = $tenantProjectMemberCountMap[$tempTenantId] ?? 0;
            $tempCoordinate = explode(",", $tempTenantAddressInfo['city_coordinate']);

            $tempAddressInfo = [
                'type' => '',
                'longitude' => '',
                'latitude' => '',
                'project_member_number' => intval($tempMemberCount),
                'tenant_name' => $tempTenantAddressInfo['company_name'],
                'tenant_address' => $tempTenantAddressInfo['company_office_address'],
                'province' => $tempTenantAddressInfo['province'],
                'cities' => $tempTenantAddressInfo['cities'],
            ];
            if (count($tempCoordinate) >= 2) {
                $tempAddressInfo['longitude'] = $tempCoordinate[0];
                $tempAddressInfo['latitude'] = $tempCoordinate[1];
            }
            if (!empty($groupMemberIdMap[$tempTenantId])) {
                $res['inside_man_number'] += $tempMemberCount;
                $tempAddressInfo['type'] = 'inside';
            } else {
                $res['outside_man_number'] += $tempMemberCount;
                $tempAddressInfo['type'] = 'outside';
            }
            if ($tempTenantId == $project['tenant_id']) {
                array_unshift($res['supplier_address_man_number_data'], $tempAddressInfo);
            } else {
                $res['supplier_address_man_number_data'][] = $tempAddressInfo;
            }
        }

        return $res;
    }

    /**
     * 项目详情
     * @param $projectId
     * @return array
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function projectDetail($projectId)
    {
        $res = [
            'project_id' => 0,
            'project_name' => '',
            'invested' => 0,
            'spent' => 0,
            'invested_unit' => '',
            'spent_unit' => '',
            'plan_episode_number' => 0,
            'done_episode_number' => 0,
            'online_episode_number' => 0,
            'task_total_number' => 0,
            'task_done_number' => 0,
            'preview_making_video_list' => [],
            'first_plan_play_time' => 0,
        ];
        $project = ProjectModel::where('id', $projectId)->find();

        $res['project_id'] = $project['id'];
        $res['project_name'] = $project['name'];
        $projectBiConfig = OptionsService::getInstance()->getProjectBIConfig($projectId);
        $res['invested'] = $projectBiConfig['invested']['value'];
        $res['spent'] = $projectBiConfig['spent']['value'];
        $res['invested_unit'] = $projectBiConfig['invested']['unit'];
        $res['spent_unit'] = $projectBiConfig['spent']['unit'];
        $res['preview_making_video_list'] = $projectBiConfig['detail_video_list'];
        $episodeList = $this->projectEpisodeList($projectId);
        $totalEpisodeCodeList = [];
        foreach ($episodeList as $episode) {
            $totalEpisodeCodeList[] = $episode['code'];
            $res['plan_episode_number'] += 1;
            if ($episode['episode_is_show'] === 'yes') {
                $res['online_episode_number'] += 1;
            }
            if ($episode['episode_is_seal'] === 'yes') {
                $res['done_episode_number'] += 1;
            }
        }
        if (!empty($projectBiConfig['episode_status_count']['done'])) {
            $res['done_episode_number'] = $projectBiConfig['episode_status_count']['done'];
        }
        if (!empty($projectBiConfig['episode_status_count']['online'])) {
            $res['online_episode_number'] = $projectBiConfig['episode_status_count']['online'];
        }
        if (!empty($projectBiConfig['episode_status_count']['plan'])) {
            $res['plan_episode_number'] = $projectBiConfig['episode_status_count']['plan'];
        }
        if (!empty($projectBiConfig['task_status_count']['total'])) {
            $res['task_total_number'] = $projectBiConfig['task_status_count']['total'];
        } else {
            $closedStatus = StatusService::getInstance()->byCode('closed');
            $res['task_total_number'] = TaskModel::join("entity", 'entity.id = task.entity_id')
                ->whereIn("entity.module_id", [
                    ModuleService::getInstance()->byCode('asset')['id'],
                    ModuleService::getInstance()->byCode('level')['id'],
                    ModuleService::getInstance()->byCode('design')['id'],
                    ModuleService::getInstance()->byCode('shot')['id'],
                    ModuleService::getInstance()->byCode('sequence')['id'],
                ])
                ->whereIn('entity.initial_episode_code', $totalEpisodeCodeList)
                ->whereRaw("get_json_string(task.`json`,'$.task_status') <> {$closedStatus['id']}")
                ->where('task.project_id', $projectId)
                ->where('task.step_type', 'step')
                ->count('task.id');
        }
        if (!empty($projectBiConfig['task_status_count']['done'])) {
            $res['task_done_number'] = $projectBiConfig['task_status_count']['done'];
        } else {
            $doneStatus = StatusService::getInstance()->byCode('final');
            $res['task_done_number'] = TaskModel::join("entity", 'entity.id = task.entity_id')
                ->whereIn("entity.module_id", [
                    ModuleService::getInstance()->byCode('asset')['id'],
                    ModuleService::getInstance()->byCode('level')['id'],
                    ModuleService::getInstance()->byCode('design')['id'],
                    ModuleService::getInstance()->byCode('shot')['id'],
                    ModuleService::getInstance()->byCode('sequence')['id'],
                ])
                ->whereIn('entity.initial_episode_code', $totalEpisodeCodeList)
                ->whereRaw("get_json_string(task.`json`,'$.task_status') = {$doneStatus['id']}")
                ->where('task.project_id', $projectId)
                ->where('task.step_type', 'step')
                ->count('task.id');
        }
        $res['first_plan_play_time'] = EntityModel::where('module_id', ModuleService::getInstance()->byCode('episode')['id'])
            ->where('project_id', $projectId)
            ->whereIn('code', $totalEpisodeCodeList)
            ->whereRaw("get_json_string(`json`,'$.episode_plan_show_time') > 0")
            ->min(new Raw("get_json_string(`json`,'$.episode_plan_show_time')"));
        return $res;
    }

    /**
     * 项目剧集列表
     * @param $projectId
     * @return EntityModel[]|array|Collection
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    private function projectEpisodeList($projectId)
    {
        $list = EntityModel::where('module_id', ModuleService::getInstance()->byCode('episode')['id'])
            ->where('project_id', $projectId)
            ->whereRaw("name like 'EP%' and length(name) = 5")
            ->field("id, name, code, `json`")
            ->select();

        foreach ($list as &$item) {
            $jsonFields = json_decode($item['json'], true);
            $item['episode_is_seal'] = $jsonFields['episode_is_seal'] ?? 'no';
            $item['episode_is_show'] = $jsonFields['episode_is_show'] ?? 'no';
        }
        return $list;
    }

    public function projectTaskDoneGroupedCount($projectId)
    {
        $res = [
            'in_progress' => 0,
            'current_overdue' => 0,
            'done' => 0,
            'total_task' => 0,
        ];
        // 查询投入/花费配置
        $projectBiConfig = OptionsService::getInstance()->getProjectBiConfig($projectId);
        $doneEpisodeCodeList = $totalEpisodeCodeList = $undoneEpisodeCodeList = [];
        $projectEpisodeList = $this->projectEpisodeList($projectId);

        foreach ($projectEpisodeList as $episode) {
            $totalEpisodeCodeList[] = $episode['code'];
            if ($episode['episode_is_seal'] == "yes") {
                $doneEpisodeCodeList[] = $episode['code'];
            } else {
                $undoneEpisodeCodeList[] = $episode['code'];
            }
        }
        if (!empty($projectBiConfig['task_status_count']['total'])) {
            $res['total_task'] = $projectBiConfig['task_status_count']['total'];
        } else {
            $closedStatus = StatusService::getInstance()->byCode('closed');
            $res['total_task'] = TaskModel::join('entity', 'entity.id = task.entity_id')
                ->where('task.project_id', $projectId)
                ->where("task.step_type = 'step'")
                ->whereIn('entity.module_id', [
                    ModuleService::getInstance()->byCode('design')['id'],
                    ModuleService::getInstance()->byCode('asset')['id'],
                    ModuleService::getInstance()->byCode('level')['id'],
                    ModuleService::getInstance()->byCode('shot')['id'],
                    ModuleService::getInstance()->byCode('sequence')['id'],
                ])
                ->whereRaw("get_json_string(task.`json`,'$.task_status') <> {$closedStatus['id']}")
                ->whereIn("entity.initial_episode_code", $totalEpisodeCodeList)
                ->count();
        }
        if (!empty($projectBiConfig['task_status_count']['done'])) {
            $res['done'] = $projectBiConfig['task_status_count']['done'];
        } else {
            $doneStatus = StatusService::getInstance()->byCode('final');
            $res['done'] = TaskModel::join('entity', 'entity.id = task.entity_id')
                ->where('task.project_id', $projectId)
                ->where("task.step_type = 'step'")
                ->whereIn('entity.module_id', [
                    ModuleService::getInstance()->byCode('design')['id'],
                    ModuleService::getInstance()->byCode('asset')['id'],
                    ModuleService::getInstance()->byCode('level')['id'],
                    ModuleService::getInstance()->byCode('shot')['id'],
                    ModuleService::getInstance()->byCode('sequence')['id'],
                ])
                ->whereRaw("get_json_string(task.`json`,'$.task_status') = {$doneStatus['id']}")
                ->whereIn("entity.initial_episode_code", $totalEpisodeCodeList)
                ->count();
        }
        if (!empty($projectBiConfig['task_status_count']['inprogress'])) {
            $res['in_progress'] = $projectBiConfig['task_status_count']['inprogress'];
        } else {
            if (count($undoneEpisodeCodeList) > 0) {
                $res['in_progress'] = TaskModel::join('entity', 'entity.id = task.entity_id')
                    ->where('task.project_id', $projectId)
                    ->where("task.step_type = 'step'")
                    ->whereIn('entity.module_id', [
                        ModuleService::getInstance()->byCode('design')['id'],
                        ModuleService::getInstance()->byCode('asset')['id'],
                        ModuleService::getInstance()->byCode('level')['id'],
                        ModuleService::getInstance()->byCode('shot')['id'],
                        ModuleService::getInstance()->byCode('sequence')['id'],
                    ])
                    ->whereRaw("get_json_string(task.`json`,'$.task_status') <> 3")
                    ->whereIn("entity.initial_episode_code", $undoneEpisodeCodeList)
                    ->whereRaw("get_json_string(task.`json`,'$.task_status') in (" . implode(',', [
                            StatusService::getInstance()->byCode('remake')['id'],
                            StatusService::getInstance()->byCode('inprogress')['id'],
                            StatusService::getInstance()->byCode('submitted')['id'],
                            StatusService::getInstance()->byCode('feedback')['id'],
                            StatusService::getInstance()->byCode('ready_to_publish')['id'],
                            StatusService::getInstance()->byCode('wait_for_party_a_review')['id'],
                            StatusService::getInstance()->byCode('wait_partyb_submit')['id'],
                        ]) . ")")
                    ->count();
            }
        }
        if ($projectBiConfig['task_status_count']['overdue'] > 0) {
            $res['current_overdue'] = $projectBiConfig['task_status_count']['overdue'];
        } else {
            if (count($undoneEpisodeCodeList) > 0) {
                $res['current_overdue'] = TaskModel::join('entity', 'entity.id = task.entity_id')
                    ->where('task.project_id', $projectId)
                    ->where("task.step_type = 'step'")
                    ->whereIn('entity.module_id', [
                        ModuleService::getInstance()->byCode('design')['id'],
                        ModuleService::getInstance()->byCode('asset')['id'],
                        ModuleService::getInstance()->byCode('level')['id'],
                        ModuleService::getInstance()->byCode('shot')['id'],
                        ModuleService::getInstance()->byCode('sequence')['id'],
                    ])
                    ->whereRaw("get_json_string(task.`json`,'$.task_status') <> 3")
                    ->whereIn("entity.initial_episode_code", $undoneEpisodeCodeList)
                    ->whereRaw("get_json_string(task.`json`,'$.task_status') in (" . implode(',', [
                            StatusService::getInstance()->byCode('remake')['id'],
                            StatusService::getInstance()->byCode('inprogress')['id'],
                            StatusService::getInstance()->byCode('submitted')['id'],
                            StatusService::getInstance()->byCode('feedback')['id'],
                            StatusService::getInstance()->byCode('ready_to_publish')['id'],
                            StatusService::getInstance()->byCode('wait_for_party_a_review')['id'],
                            StatusService::getInstance()->byCode('wait_partyb_submit')['id'],
                        ]) . ")")
                    ->whereRaw("get_json_string(task.`json`,'$.task_is_overdue') = 'yes'")
                    ->count();
            }
        }


        return $res;
    }

    private function calcBetweenDates($dStart, $dEnd)
    {
        $dates = [];
        // Specify the start date. This date can be any English textual format
        $date_from = $dStart;
        $date_from = strtotime($date_from); // Convert date to a UNIX timestamp

        // Specify the end date. This date can be any English textual format
        $date_to = $dEnd;
        $date_to = strtotime($date_to); // Convert date to a UNIX timestamp

        // Loop from the start date to end date and output all dates inbetween
        for ($i = $date_from; $i <= $date_to; $i += 86400) {
            $dates[] = date('Y-m-d', $i);
        }
        return $dates;
    }

    // 项目任务提交趋势
    public function projectTaskSubmitCountByDate($projectId, $dateStringStart, $dateStringEnd)
    {
        //res数组内元素对象结构定义
        /**
         *
         * Date               string `json:"date"`
         * VersionPassCount   int64  `json:"version_pass_count"`
         * VersionSubmitCount int64  `json:"version_submit_count"`
         */
        $dateStart = strtotime($dateStringStart);
        $dateEnd = strtotime($dateStringEnd);
        //检查日期参数格式
        if (!$dateStart || !$dateEnd) {
            return [];
        }
        $datelist = $this->calcBetweenDates($dateStringStart, $dateStringEnd);
        $res = [];
        $res_map = [];
        foreach ($datelist as $v) {
            $item = [
                "date" => $v,
                "version_pass_count" => 0,
                "version_submit_count" => 0,
            ];
            $res[] = $item;
            $res_map[$v] = $item;
        }
        //map[key=date]引用到$res的内部[$k下标]元素
        foreach ($res as $k => $v) {
            $res_map[$v['date']] =& $res[$k];
        }
        $countPassByDate = []; //[date// 日期,is_pass// 是否通过,count// 数量]
        $countPassByDate = VersionModel::where([
            ['project_id', '=', $projectId],
            ['created', '>=', $dateStart],
            ['created', '<=', $dateEnd + 86400],
        ])
            ->field("count(id) as count,FROM_UNIXTIME(created, '%Y-%m-%d') as date,is_pass")
            ->group("FROM_UNIXTIME(created,'%Y-%m-%d'),is_pass")
            ->select()
            ->toArray();
        foreach ($countPassByDate as $v) {
            $res_map[$v['date']]['version_submit_count'] += $v['count'];
            if ($v['is_pass'] == 'yes')
                $res_map[$v['date']]['version_pass_count'] += $v['count'];
        }
        return $res;
    }

    //项目环节产出情况
    public function projectStepCategoryDoneCount($projectId)
    {
        $res = [];
        $entity_module_ids = [
            ModuleService::getInstance()->byCode('design')['id'],
            ModuleService::getInstance()->byCode('asset')['id'],
            ModuleService::getInstance()->byCode('level')['id'],
            ModuleService::getInstance()->byCode('shot')['id'],
            ModuleService::getInstance()->byCode('sequence')['id'],
        ];
        //doneStatus是一个对象需要获取其包含的属性值
        $doneStatus = StatusService::getInstance()->byCode('final');
        //get_json_string(task.`json`,'$.task_status')
        $doneCountRes = TaskModel::where('project_id', $projectId)
            ->whereIn('entity_module_id', $entity_module_ids)
            ->where('step_type', 'step_category')
            ->whereRaw("get_json_string(task.`json`,'$.task_status') = {$doneStatus['id']} ")
            ->whereRaw("json_length(ifnull(get_json_string(task.`json`,'$.task_deprecate_data'),'{}')) = 0")
            ->field("step_category_id,count(id) as number")
            ->group("step_category_id")
            ->select();
        //echo $doneCountRes;
        //return [];
        $stepCategoryIdList = [];
        $doneCountResMap = [];
        foreach ($doneCountRes as $k => $v) {
            $step_category_id = $v['step_category_id'];
            $stepCategoryIdList[] = $step_category_id;
            $doneCountResMap[$step_category_id] =& $doneCountRes[$k];
        }
        $res = StepCategoryModel::whereIn('id', $stepCategoryIdList)
            ->field('id,name')
            ->order('index asc')
            ->select()
            ->toArray();
        foreach ($res as $k => $v) {
            $res[$k]['number'] = $doneCountResMap[$v['id']]['number'];
        }
        return $res;
    }

    //项目资产巡检
    public function projectAssetPreviewCheck($projectId)
    {
        $res = ["asset_category_done_task_count" => 0, "preview_making_video_list" => []];
        $entity_module_ids = [
            ModuleService::getInstance()->byCode('design')['id'],
            ModuleService::getInstance()->byCode('asset')['id'],
            ModuleService::getInstance()->byCode('level')['id'],
        ];
        //doneStatus是一个对象需要获取其包含的属性值
        $doneStatus = StatusService::getInstance()->byCode('final');
        $res['asset_category_done_task_count'] = TaskModel::whereRaw("get_json_string(task.`json`,'$.task_status') = {$doneStatus['id']}")
            ->whereIn("task.entity_module_id", $entity_module_ids)
            ->where("task.project_id", $projectId)
            ->join("entity", "entity.id = task.entity_id")
            ->join("module", "module.id = entity.module_id")
            ->fieldRaw("case module.code when 'design' then get_json_string(entity.`json`,'$.design_category') 
        when 'asset' then get_json_string(entity.`json`,'$.asset_category') 
        when 'level' then get_json_string(entity.`json`,'$.level_category') else ''
        end as name_1,count(task.id) as number")
            ->group('name_1')
            ->select();
        $biProjectConfig = OptionsService::getInstance()->getProjectBIConfig($projectId);

        $res['preview_making_video_list'] = $biProjectConfig['asset_video_list'];
        foreach ($res['asset_category_done_task_count'] as $k => $v) {
            $res['asset_category_done_task_count'][$k]['name'] = $v['name_1'];
            unset($res['asset_category_done_task_count'][$k]['name_1']);
        }
        return $res;
    }

    //项目进行中剧集
    public function projectInprogressEpisode($projectId)
    {
        $inProgressStatus = StatusService::getInstance()->byCode('inprogress')['id'];
        $episodeId = ModuleService::getInstance()->byCode('episode')['id'];
        $inprogressEpisodeList = [];
        $biProjectConfig = OptionsService::getInstance()->getProjectBIConfig($projectId);
        if (count($biProjectConfig['inprogress_episode']) > 0) {
            $inprogressEpisodeList = EntityModel::where('project_id', $projectId)
                ->where('module_id', $episodeId)
                ->whereIn("id", $biProjectConfig['inprogress_episode'])
                ->select()->toArray();
        } else {
            $inprogressEpisodeList = EntityModel::where('project_id', $projectId)
                ->where('module_id', $episodeId)
                ->whereRaw("get_json_string(`json`,'$.episode_status') = {$inProgressStatus}")
                ->whereRaw("(get_json_string(`json`,'$.episode_is_seal') = '' or get_json_string(`json`,'$.episode_is_seal') is null or get_json_string(`json`,'$.episode_is_seal') = 'no')")
                ->select()->toArray();
        }
        $episodeIdList = [];
        $inprogressEpisodeMap = [];
        foreach ($inprogressEpisodeList as $k => $v) {
            $id = $v['id'];
            $episodeIdList[] = $id;
            $inprogressEpisodeMap[$id] =& $inprogressEpisodeList[$k];
        }
        $res = TaskModel::join([
            EntityEpisodeRelationModel::where('project_id', $projectId)
                ->whereIn('episode_id', $episodeIdList)
                ->fieldRaw("distinct episode_id, entity_id")
                ->buildSql() => "epr"], "task.entity_id = epr.entity_id")
            ->field("epr.episode_id as episode_id,count(task.id) as total_task_number,count(if(get_json_string(task.`json`,'task_status') = 7, 1, null)) as undone_task_number")
            ->group("epr.episode_id")
            ->select();
        foreach ($res as $k => $v) {
            if (isset($inprogressEpisodeMap[$v['episode_id']])) {
                $res[$k]['episode_name'] = $inprogressEpisodeMap[$v['episode_id']]['name'];
            }
        }
        return $res;
    }

    /**
     * @title 因需求而新增任务统计
     * @param $params
     * @return int|string
     */
    public function demandIncTaskAnalysisResult($params){
        $project_id = $params['project_id'] ?? '';
        $step_category_id = $params['step_category_id'] ?? '';
        $time_type = 'rfe.published_time';
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        $test = $params['test'] ?? false;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res = [];
        if (empty($project_id) || empty($step_category_id) || empty($start_time) || empty($end_time)) {
            return 1;
        }
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        if (empty($start_time) || empty($end_time)) {
            return 2;
        }
        //项目ID集合
        $project_id = gettype($project_id) == 'array' ? $project_id : [$project_id];
        //环节分类ID集合
        $step_category_id = gettype($step_category_id) == 'array' ? $step_category_id : [$step_category_id];
        $project_id_list_str = implode(',', $project_id);
        $step_category_id_list_str = implode(',', $step_category_id);
        //任务库里指定项目列表+指定环节列表的非取消且step_type不在['standard_make_to_do','step_category']的任务列表
        $task_list_sql = sprintf("select from_unixtime({$time_type},'%%Y-%%m-%%d') as end_date,count(task.id) as cnt from task 
        join review_feedback_entity rfe 
        on rfe.entity_id = task.source_entity_id and rfe.is_append= 'yes' and rfe.published= 'yes'  and
        ({$time_type} between {$start_time} and {$end_time})
        where task.project_id in (%s) 
        and task.step_category_id in (%s) 
        and get_json_string(task.`json`,'$.task_status') <> $closeStatus 
        and ({$time_type} between %d and %d)
        and task.step_type not in ('standard_make_to_do','step_category') group by end_date order by end_date asc"
            , $project_id_list_str, $step_category_id_list_str, $start_time, $end_time);
        if ($test) {
            $res = $task_list_sql;
        } else {
            $res = Db::query($task_list_sql);
        }
        return $res;
    }
    /**
     * @title 因返修而新增任务统计
     * @param $params
     * @return int|string
     */
    public function repairIncTaskAnalysisResult($params){
        $project_id = $params['project_id'] ?? '';
        $step_category_id = $params['step_category_id'] ?? '';
        $time_type = 'delay_time';
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        $test = $params['test'] ?? false;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res = [];
        if (empty($project_id) || empty($step_category_id) || empty($start_time) || empty($end_time)) {
            return 1;
        }
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        if (empty($start_time) || empty($end_time)) {
            return 2;
        }
        //项目ID集合
        $project_id = gettype($project_id) == 'array' ? $project_id : [$project_id];
        //环节分类ID集合
        $step_category_id = gettype($step_category_id) == 'array' ? $step_category_id : [$step_category_id];
        $project_id_list_str = implode(',', $project_id);
        $step_category_id_list_str = implode(',', $step_category_id);
        //返修任务类型集合
        $delay_task_type_list = implode(',', ["'demand_order'"]);

        //任务库里指定项目列表+指定环节列表的非取消且step_type不在['standard_make_to_do','step_category']的任务列表
        $normal_task_list_sql = sprintf("select id from task 
        where project_id in (%s) 
        and step_category_id in (%s) 
        and get_json_string(`json`,'$.task_status') <> $closeStatus 
        and step_type not in ('standard_make_to_do','step_category')", $project_id_list_str, $step_category_id_list_str);

        //查询返修任务具体SQL
        $repair_sql = "select from_unixtime(dot.`{$time_type}`,'%Y-%m-%d') as end_date,count(dot.task_id) as cnt from delay_order_task dot 
       join ({$normal_task_list_sql}) tsk on tsk.id = dot.task_id where dot.project_id in ({$project_id_list_str}) 
       and dot.type in ({$delay_task_type_list}) 
       and dot.confirm_status = 'yes' and (dot.`{$time_type}` between {$start_time} and {$end_time}) group by end_date order by end_date asc";

        if ($test) {
            $res = $repair_sql;
        } else {
            $res = Db::query($repair_sql);
        }
        return $res;
    }
    /**
     * @title 排期分析------被取消废除的任务统计
     * @param $params
     * @return array|array[]
     */
    public function cancelTaskAnalysisResult($params){
        $project_id = $params['project_id'] ?? '';
        $step_category_id = $params['step_category_id'] ?? '';
        $time_type = 'plan_end_time';
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        $test = $params['test'] ?? false;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res = [];
        if (empty($project_id) || empty($step_category_id) || empty($start_time) || empty($end_time)) {
            return 1;
        }
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        if (empty($start_time) || empty($end_time)) {
            return 2;
        }
        //项目ID集合
        $project_id = gettype($project_id) == 'array' ? $project_id : [$project_id];
        //环节分类ID集合
        $step_category_id = gettype($step_category_id) == 'array' ? $step_category_id : [$step_category_id];
        $project_id_list_str = implode(',', $project_id);
        $step_category_id_list_str = implode(',', $step_category_id);
        //任务库里指定项目列表+指定环节列表的非取消且step_type不在['standard_make_to_do','step_category']的任务列表
        $normal_task_list_sql = sprintf("select from_unixtime(`{$time_type}`,'%%Y-%%m-%%d') as end_date,count(id) as cnt from task 
        where project_id in (%s) 
        and step_category_id in (%s) 
        and get_json_string(`json`,'$.task_status') = $closeStatus 
        and ({$time_type} between %d and %d)
        and step_type not in ('standard_make_to_do','step_category') group by end_date order by end_date asc", $project_id_list_str, $step_category_id_list_str, $start_time, $end_time);

        if ($test) {
            $res = $normal_task_list_sql;
        } else {
            $res = Db::query($normal_task_list_sql);
        }
        return $res;
    }

    /**
     * @title 排期分析------原计划任务统计
     * @param $params
     * @return array|array[]
     */
    public function originTaskAnalysisResult($params){
        $project_id = $params['project_id'] ?? '';
        $step_category_id = $params['step_category_id'] ?? '';
        $time_type = 'plan_end_time';
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        $test = $params['test'] ?? false;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res = [];
        if (empty($project_id) || empty($step_category_id) || empty($start_time) || empty($end_time)) {
            return 1;
        }
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        if (empty($start_time) || empty($end_time)) {
            return 2;
        }
        //项目ID集合
        $project_id = gettype($project_id) == 'array' ? $project_id : [$project_id];
        //环节分类ID集合
        $step_category_id = gettype($step_category_id) == 'array' ? $step_category_id : [$step_category_id];
        $project_id_list_str = implode(',', $project_id);
        $step_category_id_list_str = implode(',', $step_category_id);
        //任务库里指定项目列表+指定环节列表的非取消且step_type不在['standard_make_to_do','step_category']的任务列表
        $normal_task_list_sql = sprintf("select from_unixtime(`{$time_type}`,'%%Y-%%m-%%d') as end_date,count(id) as cnt from task 
        where project_id in (%s) 
        and step_category_id in (%s) 
        and get_json_string(`json`,'$.task_status') <> $closeStatus 
        and ({$time_type} between %d and %d)
        and step_type not in ('standard_make_to_do','step_category') group by end_date order by end_date asc", $project_id_list_str, $step_category_id_list_str, $start_time, $end_time);

        if ($test) {
            $res = $normal_task_list_sql;
        } else {
            $res = Db::query($normal_task_list_sql);
        }
        return $res;
    }
    /**
     * @title 排期分析------延期任务统计
     * @param $params
     * @return array|array[]
     */
    public function delayTaskAnalysisResult($params){
        $project_id = $params['project_id'] ?? '';
        $step_category_id = $params['step_category_id'] ?? '';
        $direction = $params['direction'] ?? 'inc';//减少 dec 新增 inc
        $time_type = $direction == "inc" ? 'delay_time' : 'original_plan_time';
        $start_time = $params['start_time'] ?? '';
        $end_time = $params['end_time'] ?? '';
        $test = $params['test'] ?? false;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res = [];
        if (empty($project_id) || empty($step_category_id) || empty($start_time) || empty($end_time)) {
            return 1;
        }
        $start_time = strtotime($start_time);
        $end_time = strtotime($end_time);
        if (empty($start_time) || empty($end_time)) {
            return 2;
        }
        //项目ID集合
        $project_id = gettype($project_id) == 'array' ? $project_id : [$project_id];
        //环节分类ID集合
        $step_category_id = gettype($step_category_id) == 'array' ? $step_category_id : [$step_category_id];
        $project_id_list_str = implode(',', $project_id);
        $step_category_id_list_str = implode(',', $step_category_id);
        //延期任务类型集合
        $delay_task_type_list = implode(',', ["'request_delay'","'work_order'", "'downstream_delay'"]);

        //任务库里指定项目列表+指定环节列表的非取消且step_type不在['standard_make_to_do','step_category']的任务列表
        $normal_task_list_sql = sprintf("select id from task 
        where project_id in (%s) 
        and step_category_id in (%s) 
        and get_json_string(`json`,'$.task_status') <> $closeStatus 
        and step_type not in ('standard_make_to_do','step_category')", $project_id_list_str, $step_category_id_list_str);

        //查询延期任务具体SQL
        $delay_sql = "select from_unixtime(dot.`{$time_type}`,'%Y-%m-%d') as end_date,count(dot.task_id) as cnt from delay_order_task dot 
       join ({$normal_task_list_sql}) tsk on tsk.id = dot.task_id where dot.project_id in ({$project_id_list_str}) 
       and dot.type in ({$delay_task_type_list}) 
       and dot.confirm_status = 'yes' and (dot.`{$time_type}` between {$start_time} and {$end_time}) group by end_date order by end_date asc";

        if ($test) {
            $res = $delay_sql;
        } else {
            $res = Db::query($delay_sql);
        }
        return $res;
    }
    //按任务原计划完成时间，统计原计划任务数量
    //原计划，延期新增，返修新增，需求新增
    //减少
    //返修
    public function projectInTaskDoneTimeCount($params){
        $project_id=$params['project_id']??'';
        $step_category_id = $params['step_category_id']??'';
        $start_time = $params['start_time']??'';
        $end_time = $params['end_time']??'';
        if(empty($project_id)||empty($step_category_id)||empty($start_time)||empty($end_time))
            return [];
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $doneStatus = [
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id'],
        ];
        $res=[
            "origin_plan"=>[],
            "today_done"=>[]
        ];
        $start_time=strtotime($start_time);
        $end_time=strtotime($end_time);
        if(!$start_time||!$end_time)
            return $res;
        $project_id=gettype($project_id)=='array'?$project_id:[$project_id];
        if(!empty($step_category_id))
        $step_category_id=gettype($step_category_id)=='array'?$step_category_id:[$step_category_id];
        $project_id_list_str=implode(',',$project_id);
        $step_category_id_list_str=implode(',',$step_category_id);
        $delay_task_type_list=implode(',',['request_delay','work_order','downstream_delay']);
        //原计划任务数量 统计
        $res['origin_plan']=TaskModel::fieldRaw("FROM_UNIXTIME(plan_end_time,'%Y-%m-%d') as end_date,FROM_UNIXTIME(plan_end_time,'%m-%d') as end_date_short,
        count(id) as cnt");
        if(!empty($step_category_id)){
            $res['origin_plan']=$res['origin_plan']->whereIn('step_category_id',$step_category_id);
        }
        $res['origin_plan']=$res['origin_plan']->whereIn("project_id",$project_id)
            ->whereBetween("plan_end_time",[$start_time,$end_time])
            ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
            ->group("`end_date`,`end_date_short`")
            ->order("end_date","asc")
            ->select();

        //延期新增 延期目标日期分组
        $res['inc_overdue']=TaskModel::fieldRaw("FROM_UNIXTIME(`dot`.`delay_time`,'%Y-%m-%d') as end_date,
       FROM_UNIXTIME(`dot`.`delay_time`,'%m-%d') as end_date_short,count(task.id) as cnt");
        if(!empty($step_category_id)){
            $res['inc_overdue']=$res['inc_overdue']->whereIn('task.step_category_id',$step_category_id);
        }
        $res['inc_overdue']=$res['inc_overdue']
            ->join("delay_order_task dot","dot.task_id = task.id")
            ->whereIn("task.project_id",$project_id)
            ->whereRaw("`dot`.`delay_time` BETWEEN $start_time AND $end_time")
            ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
            ->whereRaw("`dot`.`type` <> 'today_done' ")
            ->group("`end_date`,`end_date_short`")
            ->order("end_date","asc")
            ->select();


        //需求新增
        $res['inc_demand']=TaskModel::fieldRaw("FROM_UNIXTIME(rfe.published_time,'%Y-%m-%d') as end_date, FROM_UNIXTIME(rfe.published_time,'%m-%d') as end_date_short
        ,count(task.`id`) as cnt")
        ->join("review_feedback_entity rfe","rfe.entity_id = task.source_entity_id")
        ->whereIn("task.project_id",$project_id);
        if(!empty($step_category_id)){
            $res['inc_demand']=$res['inc_demand']->whereIn("task.step_category_id",$step_category_id);
        }
        $res['inc_demand']=$res['inc_demand']
            ->whereRaw("rfe.published_time BETWEEN $start_time AND $end_time")
            ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
            ->whereIn("rfe.is_append",'yes')
            ->group("`end_date`,`end_date_short`")
            ->order("end_date","asc")
            ->select();

        //返修新增
        $res['inc_repair']=NoteModel::fieldRaw("FROM_UNIXTIME(task.plan_end_time,'%Y-%m-%d') as end_date, FROM_UNIXTIME(task.plan_end_time,'%m-%d') as end_date_short
        ,count(task.id) as cnt")
        ->join("note_entity_relation ner","ner.note_id = note.id")
        ->join("task","task.id = ner.task_id")
        ->whereIn("task.project_id",$project_id);
        if(!empty($step_category_id)){
            $res['inc_repair']=$res['inc_repair']->whereIn("task.step_category_id",$step_category_id);
        }
        $res['inc_repair']=$res['inc_repair']->whereRaw("task.plan_end_time BETWEEN $start_time AND $end_time")
        ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
        ->whereIn("note.demand_type",['repair_feedback'])
        ->group("`end_date`,`end_date_short`")
        ->order("end_date","asc")
        ->select();

        //延期减少
        $res['dec_overdue']=TaskModel::fieldRaw("FROM_UNIXTIME(`dot`.`original_plan_time`,'%Y-%m-%d') as end_date,FROM_UNIXTIME(`dot`.`original_plan_time`,'%m-%d') as end_date_short
        ,count(dot.id) as cnt")
        ->join("delay_order_task dot","`dot`.`task_id` = `task`.`id`")
        ->whereIn("dot.project_id",$project_id);
        if(!empty($step_category_id)){
            $res['dec_overdue']=$res['dec_overdue']->whereIn("task.step_category_id",$step_category_id);
        }
        $res['dec_overdue']=$res['dec_overdue']->whereRaw("`dot`.`original_plan_time` BETWEEN $start_time AND $end_time")
        ->whereRaw("dot.type !='today_done' ")
        ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
        ->group("`end_date`,`end_date_short`")
        ->order("end_date","asc")
        ->select();

        //取消减少
        $res['dec_cancel']=TaskModel::fieldRaw("FROM_UNIXTIME(plan_end_time,'%Y-%m-%d') as end_date,FROM_UNIXTIME(plan_end_time,'%m-%d') as end_date_short
        ,count(task.id) as cnt")
            ->whereIn("project_id",$project_id);
        if(!empty($step_category_id)){
            $res['dec_cancel']=$res['dec_cancel']->whereIn("task.step_category_id",$step_category_id);
        }
        $res['dec_cancel']=$res['dec_cancel']->whereRaw("plan_end_time BETWEEN $start_time AND $end_time")
            ->whereRaw("get_json_string(task.`json`,'$.task_status') = $closeStatus")
            ->group("`end_date`,`end_date_short`")
            ->order("end_date","asc")
            ->select();

        //今日完成
        $res['today_done']=TaskModel::fieldRaw("FROM_UNIXTIME(plan_end_time,'%Y-%m-%d') as end_date,
       FROM_UNIXTIME(plan_end_time,'%m-%d') as end_date_short,count(id) as cnt")
            ->whereIn("project_id",$project_id);
        if(!empty($step_category_id)){
            $res['today_done']=$res['today_done']->whereIn("task.step_category_id",$step_category_id);
        }
        $res['today_done']=$res['today_done']->whereRaw("plan_end_time BETWEEN UNIX_TIMESTAMP(DATE_FORMAT(now(),'%Y-%m-%d 00:00:00')) AND UNIX_TIMESTAMP(DATE_FORMAT(now(),'%Y-%m-%d 23:59:59')) ")
            ->whereRaw("get_json_string(task.`json`,'$.task_status') in (".implode(',',$doneStatus).")")
            ->where("task.step_type","step")
            ->group("`end_date`,`end_date_short`")
            ->order("end_date","asc")
            ->select();

        return $res;
    }
    public function projectMeetingReviewCount($params){
        $project_id=$params['project_id']??'';
        $step_category_id = $params['step_category_id']??'';
        $start_time = $params['start_time']??'';
        $end_time = $params['end_time']??'';
        if(empty($project_id)||empty($start_time)||empty($end_time))
            return [];
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $res=["meeting"=>[],"review"=>[]];
        $start_time=strtotime($start_time);
        $end_time=strtotime($end_time);
        if(!$start_time||!$end_time)
            return $res;
        $project_id=gettype($project_id)=='array'?$project_id:[$project_id];
        if(!empty($step_category_id))
        $step_category_id=gettype($step_category_id)=='array'?$step_category_id:[$step_category_id];
        //生成任务关联标签表子表SQL
        $trtJoinSql=TaskRelationTagModel::where("link_module_code","episode")->field("task_id,link_id")->buildSql();

        //任务表关联上面生成的子表SQL,获取任务名称，任务关联剧集code,任务的创建日期进行分组
        $res['meeting']=TaskModel::join([$trtJoinSql=>"trt"],"task.id = trt.task_id")
            ->join("entity","entity.id = trt.link_id")
            ->fieldRaw("task.name as task_name,task.id as task_id,entity.code as episode,FROM_UNIXTIME(task.plan_end_time,'%Y-%m-%d') 
            as end_time_format,FROM_UNIXTIME(task.plan_end_time,'%w') 
            as end_time_format_short,case (GET_JSON_STRING(entity.`json`,'$.episode_is_seal')) when 'yes' then 'yes' else 'no' end as episode_is_seal"
            )
            ->whereIn("task.project_id",$project_id);
        if(!empty($step_category_id)){
            $res['meeting']=$res['meeting']->whereIn("task.step_category_id",$step_category_id);
        }
        //任务表的step_type==standard_meeting的任务
        $res['meeting']=$res['meeting']->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
            ->whereBetween("task.plan_end_time",[$start_time,$end_time])
            ->whereIn("task.step_type",['standard_meeting'])
            ->group("`episode`,task.id,task.name,end_time_format,end_time_format_short,episode_is_seal")
            ->order("end_time_format","asc")
            ->select();

        $res['review']=TaskModel::join([$trtJoinSql=>"trt"],"task.id = trt.task_id")
            ->join("entity","entity.id = trt.link_id")
            ->fieldRaw("task.name as task_name,task.id as task_id,entity.code as episode,FROM_UNIXTIME(task.plan_end_time,'%Y-%m-%d') 
            as end_time_format,FROM_UNIXTIME(task.plan_end_time,'%w') 
            as end_time_format_short,case (GET_JSON_STRING(entity.`json`,'$.episode_is_seal')) when 'yes' then 'yes' else 'no' end as episode_is_seal")
            ->whereIn("task.project_id",$project_id);
        if(!empty($step_category_id)){
            $res['review']=$res['review']->whereIn("task.step_category_id",$step_category_id);
        }
        //任务表的step_type==standard_review的任务
        $res['review']=$res['review']->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus")
            ->whereBetween("task.plan_end_time",[$start_time,$end_time])
            ->whereIn("task.step_type",['standard_review'])
            ->group("`episode`,task.id,task.name,end_time_format,end_time_format_short,episode_is_seal")
            ->order("end_time_format","asc")
            ->select();
        return $res;
    }
    public function projectEpisodeCurrentProgressInTime($params){
        $project_id=$params['project_id']??'';
        $start_time = $params['start_time']??'';
        $end_time = $params['end_time']??'';
        $page = $params['page']??'1';
        $pagesize = $params['pagesize']??'20';
        if(empty($project_id)||empty($start_time)||empty($end_time))
            return [];
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $res=[];
        $start_time=strtotime($start_time);
        $end_time=strtotime($end_time);
        if(!$start_time||!$end_time)
            return $res;
        $project_id=gettype($project_id)=='array'?$project_id:[$project_id];
        $trtJoinSql=TaskRelationTagModel::where("link_module_code","episode")->field("task_id,link_id")->buildSql();
        $res = TaskModel::whereIn("task.project_id",$project_id)
            ->where("task.plan_end_time",">",120000)
            ->whereRaw("get_json_string(entity.`json`,'$.episode_status') <> $closeStatus")
            ->whereRaw("get_json_string(task.`json`,'$.task_status') <> $closeStatus");
        $res=$res->fieldRaw("entity.code as episode_code,task.id as task_id,task.name as task_name,
        FROM_UNIXTIME(task.plan_end_time,'%Y-%m-%d') as end_time_format,FROM_UNIXTIME(task.plan_end_time,'%w') as end_time_format_short")
            ->join([$trtJoinSql=>"trt"],"trt.task_id = task.id")
            ->join("entity","entity.id = trt.link_id")
            ->whereBetween("task.plan_end_time",[$start_time,$end_time])
            ->group("entity.code,task.id,task.name,FROM_UNIXTIME(task.plan_end_time,'%Y-%m-%d'),FROM_UNIXTIME(task.plan_end_time,'%w')")
            ->order(["end_time_format"=>"desc"])
            ->limit(($page-1)*$pagesize,$pagesize)
            ->select();
        return $res;
    }
    public function projectEpisodesStepCountResutlList($params){
        $res=[];
        $project_id=$params['project_id']??'';
        $start_time=$params['start_time']??'';
        $end_time=$params['end_time']??'';
        $test=$params['test']??'';
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $episode_module_id=ModuleService::getInstance()->byCode('episode','id');
        $normalWhere="GET_JSON_STRING(`json`,'$.%s_status') <> $closeStatus";
        $inProgressStatus = [
            StatusService::getInstance()->byCode('not_started')['id'],
            StatusService::getInstance()->byCode('ready_to_start')['id'],
            StatusService::getInstance()->byCode('inprogress')['id'],
        ];
        $inProgressWhere="GET_JSON_STRING(`json`,'$.%s_status') in (".implode($inProgressStatus,',').")";
        $project=ProjectModel::where("id",$project_id)
            ->whereRaw(sprintf($normalWhere,'project'))
            ->find();
        if(empty($project))
            return $res;
        $episodes_ids=EntityModel::where("project_id",$project_id)
            ->where('module_id',$episode_module_id)
            ->whereRaw(sprintf($normalWhere,'episode'))
            ->column("id");

        $reviewfeedback_stage_list="select rfe.initial_episode_code,rf.stage_id as episode_code from review_feedback_entity rfe join rfe 
        (select id from review_feedback where prject_id = {$project_id}) rf on rf.id = rfe.review_feedback_id";

        $raw_episodes_ids=implode(',',$episodes_ids);

        $episode_task_ids="select link_id,task_id from task_relation_tag 
        join task on task.id=task_relation_tag.task_id where link_module_code='episode' 
                                                         and link_id in({$raw_episodes_ids}) 
                                                         and ".sprintf($normalWhere,'task');

        $inprogress_episode_task_ids="select link_id,task_id from task_relation_tag 
        join task on task.id=task_relation_tag.task_id where link_module_code='episode' 
                                                         and link_id in({$raw_episodes_ids}) 
                                                         and ".sprintf($inProgressWhere,'task');

        $total_task_count="select entity.`code`,
       count(e_t_id_list.task_id) as cnt from entity 
       join ({$episode_task_ids}) e_t_id_list on e_t_id_list.link_id = entity.id 
       group by entity.`code`";

        $inprogress_task_count="select entity.`code`,
       count(e_t_id_list.task_id) as cnt from entity 
       join ({$inprogress_episode_task_ids}) e_t_id_list on e_t_id_list.link_id = entity.id 
       group by entity.`code`";

        $has_workorder_task_count="select entity.`code`,count(two.task_id) as cnt from entity 
        join ({$episode_task_ids}) e_t_id_list on e_t_id_list.link_id = entity.id 
        left join task_work_order two on two.task_id = e_t_id_list.link_id and two.status <> 2 
        group by entity.`code`";

        $has_delay_task_count="select entity.`code`,count(dot.task_id) as cnt from entity 
        join ({$episode_task_ids}) e_t_id_list on e_t_id_list.link_id = entity.id 
        left join delay_order_task dot on dot.task_id = e_t_id_list.link_id and dot.type!='today_done' <> 2 
        group by entity.`code`";

        $is_append_task_count="select entity.`code`,count(e_t_id_list.link_id) as cnt from entity 
        join ({$episode_task_ids} and GET_JSON_STRING(`json`,'$.task_is_append') = 'yes') e_t_id_list on e_t_id_list.link_id = entity.id 
        group by entity.`code`";

        if($test)
        return compact('project','episodes_ids','episode_task_ids',
            'total_task_count','inprogress_task_count','has_workorder_task_count',
            'has_delay_task_count','is_append_task_count');
        return $res;
    }
    /**
     * @param $params
     * params = {
        project_id,
     *  episode_code
     * step_category_id
     * }
     * @return EntityModel|array|Model
     * @throws DataNotFoundException
     * @throws DbException
     * @throws ModelNotFoundException
     */
    public function projectEpisodeCurrentProgressInStep_Item($params){
        /**
         * 获取Controller传参
         * project_id 项目id
         * episode 剧集code
         * step_category_id 阶段总类id
         *
         *
         */
        $project_id=$params['project_id']??'';
        $episode_id=$params['episode']??'';
        $step_category_id=$params['step_category_id']??'';
        $start_time = $params['start_time']??'';
        $end_time = $params['end_time']??'';
        if(empty($project_id)||empty($start_time)||empty($end_time)||empty($step_category_id))
            return [];
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $inProgressStatus = [
            StatusService::getInstance()->byCode('inprogress')['id'],
            StatusService::getInstance()->byCode('feedback')['id'],
            StatusService::getInstance()->byCode('remake')['id']
        ];
        $notDoneStatus = [
            $closeStatus,
            StatusService::getInstance()->byCode('final')['id'],
            StatusService::getInstance()->byCode('ready_to_publish')['id']
        ];
        $res=[];
        $start_time=strtotime($start_time);
        $end_time=strtotime($end_time);
        if(!$start_time||!$end_time)
            return $res;

        $project_id=gettype($project_id)=='array'?$project_id:[$project_id];
        $project_id=implode($project_id,',');
        $step_category_id=gettype($step_category_id)=='array'?$step_category_id:[$step_category_id];
        $step_category_id=implode($step_category_id,',');
        $episode_module_id=ModuleService::getInstance()->byCode('episode','id');
        $projects=ProjectModel::whereIn('id',$project_id)->select();
        $inProgressStatus=implode(',',$inProgressStatus);
        $notDoneStatus=implode(',',$notDoneStatus);

        //获取剧集对象实体
        $episode=EntityModel::whereIn("project_id",$project_id)
            ->where("module_id",$episode_module_id)
            ->where("code",$episode_id)
            ->find();

        //生成 剧集 实体的所有 正常任务 的 id SQL
        $task_ids="select id from task where project_id in ({$project_id}) and id in (select task_id from task_relation_tag where link_module_code='step_category' 
                     and task_id in (select task_id from task join task_relation_tag on task_relation_tag.task_id=task.id and task_relation_tag.link_module_code='episode' 
                     and task_relation_tag.link_id={$episode['id']}) and link_id in({$step_category_id})) and GET_JSON_STRING(task.`json`,'$.task_status') <> {$closeStatus} 
                     and step_type in ('step','to_do','work_order_to_do','entity_to_do')";

        //生成 剧集 实体的所有 正常任务 的 entity_id SQL
        $task_entity_ids="select entity_id from task where project_id in ({$project_id}) and id in (select task_id from task_relation_tag where link_module_code='step_category' 
                     and task_id in (select task_id from task join task_relation_tag on task_relation_tag.task_id=task.id and task_relation_tag.link_module_code='episode' 
                     and task_relation_tag.link_id={$episode['id']}) and link_id in({$step_category_id})) and GET_JSON_STRING(task.`json`,'$.task_status') <> {$closeStatus} 
                     and step_type in ('step','to_do','work_order_to_do','entity_to_do')";

        //生成 剧集 实体的所有 正常任务 的 count统计 SQL
        $task_count="select count(id) as cnt from task where project_id in ({$project_id}) and id in (select task_id from task_relation_tag where link_module_code='step_category' 
                     and task_id in (select task_id from task join task_relation_tag on task_relation_tag.task_id=task.id and task_relation_tag.link_module_code='episode' 
                     and task_relation_tag.link_id={$episode['id']}) and link_id in({$step_category_id})) and GET_JSON_STRING(task.`json`,'$.task_status') <> {$closeStatus} 
                     and step_type in ('step','to_do','work_order_to_do','entity_to_do')";

        //生成 剧集 实体的所有 进行中状态正常任务 的 count统计 SQL
        $inprogress_count="select count(id) as cnt from task where project_id in ({$project_id}) and id in (select task_id from task_relation_tag where link_module_code='step_category' 
                     and task_id in (select task_id from task join task_relation_tag on task_relation_tag.task_id=task.id and task_relation_tag.link_module_code='episode' 
                     and task_relation_tag.link_id={$episode['id']}) and link_id in({$step_category_id})) and GET_JSON_STRING(task.`json`,'$.task_status') not in ({$notDoneStatus}) 
                     and step_type in ('step','to_do','work_order_to_do','entity_to_do')";

        //生成 剧集 实体的所有 计划交付时间超时的任务 SQL
        $delivery_delay_list="select id from task where project_id in ({$project_id}) and id in (select task_id from task_relation_tag where link_module_code='step_category' 
                     and task_id in (select task_id from task join task_relation_tag on task_relation_tag.task_id=task.id and task_relation_tag.link_module_code='episode' 
                     and task_relation_tag.link_id={$episode['id']}) and link_id in({$step_category_id})) and GET_JSON_STRING(task.`json`,'$.task_status') <> {$closeStatus} 
                     and step_type in ('step','to_do','work_order_to_do','entity_to_do') and plan_delivery_time < UNIX_TIMESTAMP()";

        //生成 剧集 实体的所有 异常的 延期 正常任务 的  SQL
        $delay_list="select delay_order_task.* from delay_order_task join ({$task_ids}) taskids on taskids.id=delay_order_task.task_id 
         where delay_order_task.project_id in ({$project_id}) 
                          and delay_order_task.step_category_id  in({$step_category_id}) and delay_order_task.type!='today_done'";
        $delay_count="select count(delay_list.id) as cnt from ({$delay_list}) delay_list";

        //生成 剧集 实体的所有 异常的 存在工单 正常任务 的  SQL
        $task_work_list="select task_work_order.* from task_work_order join ({$task_ids}) taskids on taskids.id=task_work_order.task_id where task_work_order.project_id 
                         in ({$project_id}) 
                          and task_work_order.step_category_id in ({$step_category_id}) ";
        $task_work_count="select count(task_work_list.id) as cnt from ({$task_work_list}) task_work_list";

        //生成 剧集 实体的 当前阶段(stage) 的  SQL
        $current_stage="select `name` from stage join (select stage_id from review_feedback where project_id in ({$project_id}) 
                        and array_contains(split(episode_codes,','),'{$episode['code']}') 
                        and stage_id > 0 
                        order by stage_id desc) t_feedback on t_feedback.stage_id = stage.id order by order_number desc limit 1";

        //生成 剧集 当前阶段(step_category) 异常的 影响下游任务数量的  SQL
        $effect_downstream_count_sql="select count(id) as cnt from task where get_json_string(task.`json`, '$.task_status') <> {$closeStatus} and entity_id in 
    (select entity.id from entity join module on module.id = entity.module_id where entity.project_id in ({$project_id})
    and (FIND_IN_SET('%s', get_json_string(entity.`json`, CONCAT('$.', module.`code`, '_asset'))) <> 0 or 
         FIND_IN_SET('%s', get_json_string(entity.`json`, CONCAT('$.', module.`code`, '_level'))) <> 0 or 
         entity.id = %s
        )
    and get_json_string(entity.`json`, CONCAT('$.', module.`code`, '_status')) <> '{$closeStatus}')
    and step_id in (
    select step_id from step_upstream 
     where get_json_string(config,'$.step_id') = %d 
     and project_template_id = {$projects[0]['project_template_id']}
    )";

        //生成 延期+存在工单的任务 的 实体entity_id SQL
        $delay_task_entity_list="select task.entity_id,task.step_id from task join ({$delay_list}) dot on dot.task_id=task.id";
        $taskwork_task_entity_list="select task.entity_id,task.step_id from task join ({$task_work_list}) two on two.task_id=task.id";


        //查库 上面生成的 延期+存在工单的任务 的 实体entity_id
//        $delay_task_entity_list_result=Db::query($delay_task_entity_list);
//        $taskwork_task_entity_list_result=Db::query($taskwork_task_entity_list);

        $sqls=["delay"=>[],"taskwork"=>[]];

        //循环替换SQL字符串模板变量
//        foreach($delay_task_entity_list_result as $v){
//            $sqls["delay"][]=vsprintf($effect_downstream_count_sql,[$v['entity_id'],$v['entity_id'],$v['entity_id'],$v['step_id']]);
//        }
//        //循环替换SQL字符串模板变量
//        foreach($taskwork_task_entity_list_result as $v){
//            $sqls["taskwork"][]=vsprintf($effect_downstream_count_sql,[$v['entity_id'],$v['entity_id'],$v['entity_id'],$v['step_id']]);
//        }

        //生成 所有的数量查询 SQL UNION在一起 批量查询
        $effect_downstream_count_union_sql="";
//        foreach($sqls['delay'] as $v){
//            if(!empty($effect_downstream_count_union_sql))
//                $effect_downstream_count_union_sql.=" union {$effect_downstream_count_union_sql}";
//            else
//                $effect_downstream_count_union_sql=$v;
//        }
//        foreach($sqls['taskwork'] as $v){
//            if(!empty($effect_downstream_count_union_sql))
//                $effect_downstream_count_union_sql.=" union {$effect_downstream_count_union_sql}";
//            else
//                $effect_downstream_count_union_sql=$v;
//        }



        $episode['total_task']=Db::query($task_count);
        $episode['total_task']=empty($episode['total_task'])?0:$episode['total_task'][0];

        $episode['inprogress_count']=Db::query($inprogress_count);
        $episode['inprogress_count']=empty($episode['inprogress_count'])?0:$episode['inprogress_count'][0];

        $episode['delay_count']=Db::query($delay_count);
        $episode['delay_count']=empty($episode['delay_count'])?0:$episode['delay_count'][0];

        $episode['task_work_count']=Db::query($task_work_count);
        $episode['task_work_count']=empty($episode['task_work_count'])?0:$episode['task_work_count'][0];

        $episode['current_stage']=Db::query($current_stage);
        $episode['current_stage']=empty($episode['current_stage'])?0:$episode['current_stage'][0];

        if(!empty($effect_downstream_count_union_sql)){
            $episode['effect_downstream_count']=Db::query("select sum(t.cnt) as cnt from ({$effect_downstream_count_union_sql}) t");
            $episode['effect_downstream_count']=empty($episode['effect_downstream_count'])?0:$episode['effect_downstream_count'][0];
        }else{
            $episode['effect_downstream_count']=['cnt'=>0];
        }

        if(isset($params['test'])&&$params['test']==true)
            return compact('task_ids','task_entity_ids','delay_list','task_work_list','delay_task_entity_list','taskwork_task_entity_list',"sqls",'delivery_delay_list');
        return $episode;


    }
    public function getOpenTemplateTodoStageList($params)
    {
        //$projectId, $page, $isOpen = true
        $requestData=[
            'param' => [
                'filter' => [
                    'project_id' => $params['project_id']??''
                ]
            ]
        ];
        return teamones_request('teamones-saas', 'template_todo/get_open_template_todo_stage_list', $requestData)['data'];
    }
    public function projectEpisodeStageList($params){
        $res=[];
        $project_id=$params['project_id']??'';
        $episode_code=$params['episode_code']??'';
        $stage_id=$params['stage_id']??'';
        $test = $params['test']??'';
        $current_time=time();//当前时间
        if(empty($project_id))
            return $res;
        $closeStatus = StatusService::getInstance()->byCode('closed')['id'];
        $finalStatus = StatusService::getInstance()->byCode('final')['id'];
        $inProgressStatus = StatusService::getInstance()->byCode('inprogress')['id'];
        $stages=$this->getOpenTemplateTodoStageList(['project_id'=>$project_id]);
        if(empty($stages))
            return $res;
        $episode_module_id=ModuleService::getInstance()->byCode('episode','id');
        $episode_entity=EntityModel::where("project_id",$project_id)
            ->where("module_id",$episode_module_id)
            ->where("code",$episode_code)
            ->find();
        if(empty($episode_entity))
            return $res;
        $review_feedback_list=ReviewFeedbackModel::where("project_id",$project_id)
            ->where('stage_id',$stage_id)
            ->where('module_code','shot')
            ->where('demand_type','demand_order')
            ->whereLike('episode_codes',"%{$episode_code}%")
            ->select();
        foreach ($review_feedback_list as &$v){
            $v['entity_list']=Db::query("select * from review_feedback_entity where review_feedback_id = {$v['id']}");
        }
        $episode_review_feedback_entity['list']=$review_feedback_list;
        $episode_review_feedback_entity['all']="select rfe.entity_id from review_feedback_entity rfe 
        join review_feedback rf on rf.id= rfe.review_feedback_id 
        where rf.stage_id={$stage_id} and rf.module_code='shot' 
        and rfe.initial_episode_code='{$episode_code}' and rfe.enable!='no'";

        $episode_review_feedback_entity['task']="select * from task 
        join({$episode_review_feedback_entity['all']})  e_rfe on e_rfe.entity_id = task.source_entity_id 
        where task.step_type not in ('step_category','show_step_category')";

        $episode_review_feedback_entity['append']="select rfe.entity_id from review_feedback_entity rfe 
        join review_feedback rf on rf.id= rfe.review_feedback_id 
        where rf.stage_id={$stage_id} and rf.module_code='shot' 
        and rfe.initial_episode_code='{$episode_code}' and rfe.is_append='yes' and rfe.enable!='no'";

        $episode_review_feedback_entity['repair']="select ner.* from note_entity_relation ner 
        join note on note.id = ner.note_id
        join ({$episode_review_feedback_entity['all']}) e_rfe 
        on e_rfe.entity_id = ner.entity_id 
        where note.demand_type = 'repair_feedback' and note.demand_status = 'processing'";

        $episode_review_feedback_entity['overdue']="select task.*,dot.delay_time-dot.original_plan_time as times from task 
        join ({$episode_review_feedback_entity['all']}) e_rfe  
        on e_rfe.entity_id = task.source_entity_id 
        join delay_order_task dot on dot.task_id = task.id and dot.type in ('request_delay', 'work_order')
        join delay_order dor on dor.id= dot.delay_order_id where GET_JSON_STRING(task.`json`,'$.task_status') <> $closeStatus";

        $episode_review_feedback_entity['all_cnt']="select count(*) as cnt from({$episode_review_feedback_entity['all']}) t";
        $episode_review_feedback_entity['append_cnt']="select count(*) as cnt from({$episode_review_feedback_entity['append']}) t";
        $episode_review_feedback_entity['repair_cnt']="select count(id) as cnt from({$episode_review_feedback_entity['repair']}) t";
        $episode_review_feedback_entity['overdue_cnt']="select count(id) as cnt from({$episode_review_feedback_entity['overdue']}) t";
        $episode_review_feedback_entity['overdue_days']="select CAST(sum(times)/(60*60*24) as DECIMAL(11,2)) as delay_days from({$episode_review_feedback_entity['overdue']}) t";
        $episode_review_feedback_entity['task_cnt']="select count(*) as cnt from({$episode_review_feedback_entity['task']}) t";
        if($test)
        return $episode_review_feedback_entity;
        else{
            $res['episode_stage_review_feedback_list']=$review_feedback_list;
            $res['episode_stage_review_feedback_entity_cnt']=Db::query($episode_review_feedback_entity['all_cnt'])[0]['cnt'];
            $res['episode_stage_review_feedback_task_cnt']=Db::query($episode_review_feedback_entity['task_cnt'])[0]['cnt'];
            $res['episode_stage_review_feedback_append_cnt']=Db::query($episode_review_feedback_entity['append_cnt'])[0]['cnt'];
            $res['episode_stage_review_feedback_repair_cnt']=Db::query($episode_review_feedback_entity['repair_cnt'])[0]['cnt'];
            //$res['episode_stage_review_feedback_overdue_entity']=Db::query($episode_review_feedback_entity['overdue_cnt']);
            $res['episode_stage_review_feedback_overdue_days']=Db::query($episode_review_feedback_entity['overdue_days'])[0]['delay_days'];
        }
        return $res;
    }

    /**
     * 查询分类的工序任务模板列表
     * @param $params
     * @return Array
     */
    public function queryStepCategoryWorkflowTemplatesStepList($params){
        $project_id=$params['project_id'];
        $step_category_id=$params['step_category_id'];
        $test=$params['test'];
        $project_template_id=ProjectModel::where('id',$project_id)->value('project_template_id');

        //!!!（没有表）线上没有step_workflow表
        $list="select sw.* from step_workflow sw 
            join step_category sc on sc.id = sw.step_category_id 
            where sw.is_abnormal='no' 
            and sw.project_template_id={$project_template_id} 
            and sw.step_category_id={$step_category_id}";
        if($test)
            return $list;
        return Db::query($list);
    }
    public function queryTopLevelStepCategoryList($params){
        $step_category_id=$params['step_category_id'];
        $test=$params['test'];
        $list=[];
        if(empty($step_category_id)){
            $list="select * from step_category where parent_id = 0";
        }else{
            $list="select * from step_category where parent_id = {$step_category_id}";
        }
        if($test)
            return $list;
        return Db::query($list);
    }

    /**
     * @title 查询环节下的工序，其上游工序列表
     * @param $params
     * @return array
     */
    public function queryStepCategoryStepsUpstreamSteps($params){
        $project_id=$params['project_id'];
        $step_category_id=$params['step_category_id'];
        $workflow_id=$params['workflow_id'];
        $test=$params['test'];
//        $step_category_id=gettype($step_category_id)=='array'?$step_category_id:[$step_category_id];
//        $step_category_id=implode($step_category_id,',');

        $list=[];
        $project_template_id=ProjectModel::where('id',$project_id)->value('project_template_id');
        $step_category_follow_list="select * from step_category where parent_id = {$step_category_id}";
        $step_list="select step.id from step join ({$step_category_follow_list}) scfl on scfl.id= step.step_category_id";
        $step_upstram_list="";
        //!!!（没有字段）线上step_upstream表暂没有step_workflow_id字段
        if(!empty($workflow_id)) {
            $step_upstram_list = "select stl.id as current_step_id,GET_JSON_STRING(stpups.`config`,'$.step_id') as upstream_step_id from step_upstream stpups 
        join ({$step_list}) stl on stl.id = stpups.step_id
        where stpups.project_template_id = {$project_template_id} and stpups.step_workflow_id = {$workflow_id}";
        }else{
            $step_upstram_list = "select stl.id as current_step_id,GET_JSON_STRING(stpups.`config`,'$.step_id') as upstream_step_id from step_upstream stpups 
        join ({$step_list}) stl on stl.id = stpups.step_id
        where stpups.project_template_id = {$project_template_id}";
        }
        if($test)
            return compact('step_category_follow_list','step_list','step_upstram_list');
        return Db::query($step_upstram_list);
    }
    /**
     * @title 查询环节下的工序，其下游工序列表
     * @param $params
     * @return array
     */
    public function queryStepCategoryStepsDownstreamSteps($params){
        $project_id=$params['project_id'];
        $step_category_id=$params['step_category_id'];
        $workflow_id=$params['workflow_id'];
        $test=$params['test'];
        $list=[];
        $project_template_id=ProjectModel::where('id',$project_id)->value('project_template_id');
        $step_category_follow_list="select * from step_category where parent_id = {$step_category_id}";
        $step_list="select step.id from step join ({$step_category_follow_list}) scfl on scfl.id= step.step_category_id";
        //!!!（没有字段）线上step_upstream表暂没有step_workflow_id字段
        $step_downstram_list="";
        if(!empty($workflow_id)) {
            $step_downstram_list="select stl.id as current_step_id,stpups.step_id as downstream_step_id from step_upstream stpups 
        join ({$step_list}) stl on stl.id = GET_JSON_STRING(stpups.`config`,'$.step_id')
        where stpups.project_template_id = {$project_template_id} and stpups.step_workflow_id = {$workflow_id}";
        }else{
            $step_downstram_list="select stl.id as current_step_id,stpups.step_id as downstream_step_id from step_upstream stpups 
        join ({$step_list}) stl on stl.id = GET_JSON_STRING(stpups.`config`,'$.step_id')
        where stpups.project_template_id = {$project_template_id}";
        }
        if($test)
            return compact('step_category_follow_list','step_list','step_downstram_list');
        return Db::query($step_downstram_list);
    }

    /**
     * @title 查询当前集的所处环节分类下有影响问题的的任务数量
     * @param $params
     * @return array
     */
    public function queryCurrentStepCategoryEffectTaskList($params){
        $close_status=StatusService::getInstance()->byCode('closed')['id'];
        $final_status=StatusService::getInstance()->byCode('final')['id'];
        $project_id=$params['project_id'];//项目id
        $episode_code=$params['episode_code'];//剧集code
        $step_category_id=$params['step_category_id'];//一级环节分类id[剧本27-剧本29] 剧本27的ID
        $objectname=$params['objectname'];//获取 具体的 对象名称(entity|cross_entity|task)
        $test=$params['test'];
        $episode_id=EntityModel::where('code',$episode_code)
        ->where('project_id',$project_id)
        ->whereRaw("GET_JSON_STRING(`json`,'$.episode_status') <> $close_status")
        ->value('id');
        //大环节下的子环节列表SQL
        $step_category_follow_list="select * from step_category where parent_id = {$step_category_id}";
        //子环节的所有工序列表SQL
        $step_list="select step.id from step join ({$step_category_follow_list}) scfl on scfl.id= step.step_category_id";
        //所有环节下有问题的任务列表
        $current_stepcategory_problem_task_list="select * from task 
         where project_id = {$project_id} 
         and episode_id = {$episode_id} 
         and step_type='step' 
         and GET_JSON_STRING(`json`,'$.task_status') not in ({$close_status},{$final_status}) 
         and plan_delivery_time > 0
         and delivery_time > plan_delivery_time";
        //问题任务列表的工序与工序列表ID进行JOIN
        $sql="";
        //!!!（没有表）线上没有 entity_relationships 表
        switch ($objectname){
            case "entity":{
                $sql="select tsk.entity_id as `id` from ({$current_stepcategory_problem_task_list}) tsk 
              join ({$step_list}) stp on stp.id = tsk.step_id group by tsk.entity_id";
                break;
            }
            case "cross_entity":{
                $in_sql="select tsk.entity_id as `id` from ({$current_stepcategory_problem_task_list}) tsk 
              join ({$step_list}) stp on stp.id = tsk.step_id group by tsk.entity_id";
                $sql="select relation_entity_id as `id`  from entity_relationships where entity_id in ({$in_sql})  GROUP BY relation_entity_id";
                break;
            }
            case "task":{
                $sql="select tsk.id as `id`  from ({$current_stepcategory_problem_task_list}) tsk 
              join ({$step_list}) stp on stp.id = tsk.step_id";
            }
            default:{

            }
        }
        if(empty($sql))return [];
        if($test) {
            return $sql;
        }else{
            return Db::query($sql);;
        }
    }
}
